<?php
/**
 * 构建各种查询语句
 *
 */

namespace ulue\core\db\mysql\query;

use Debug;

use ulue\core\db\base\query\AbstractSqlBuilder;
use ulue\core\db\QueryElement;

class SqlBuilder extends AbstractSqlBuilder
{
    static protected $driver='mysql';

    const PRIMARY  = 'PRIMARY KEY';
    const INDEX    = 'INDEX';
    const UNIQUE   = 'UNIQUE';
    const SPATIAL  = 'SPATIAL';
    const FULLTEXT = 'UNIQUE';
    const FOREIGN  = 'FOREIGN KEY';



    static public function showDatabase($where=null)
    {
        $where = $where ? new QueryElement('WHERE', $where, 'AND') : null;

        return 'SHOW DATABASES ' . $where;
    }

    static public function createDatabase($name, $isNotExists = false, $charset = null, $collate = null)
    {
        $query = static::getQuery();

        return static::build(
            'CREATE DATABASE',
            $isNotExists ? 'IF NOT EXISTS' : null,
            $query->quoteName($name),
            $charset ? 'CHARACTER SET=' . $query->quote($charset) : null,
            $collate ? 'COLLATE=' . $query->quote($collate) : null
        );
    }


    /**
     * @param string $db
     * @param bool   $ifExist
     *
     * @return  string
     */
    public static function dropDatabase($db, $ifExist = false)
    {
        $query = static::getQuery();

        return static::build(
            'DROP DATABASE',
            $ifExist ? 'IF EXISTS' : null,
            $query->quoteName($db)
        );
    }

    public function setCharset($charset='utf8')
    {
        return 'SET NAMES '.$charset;
    }

    // mysql: SHOW TABLES;
    static public function showDbTables($database=null)
    {
        $query = static::getQuery();

        return static::build(
            'SHOW TABLES',
            $database ? 'FROM ' . $query->quoteName($database) : null
        );
    }

    // mysql: show table status from `blog` where `Name`='tbl_lookup';
    // mysql: show table status from `blog` where `Enigne`='InnoDB';
    static public function showDbTableStatus($database, $where=null)
    {
        $query = static::getQuery();

        return static::build(
            'SHOW',
            'TABLE STATUS FROM',
            $query->quoteName($database),
            $where ? new QueryElement('WHERE', $where, 'AND') : null
        );
    }

    /**
     * sql: SHOW FULL COLUMNS FROM `testDb`.`testTable`;
     * sql: SHOW COLUMNS FROM `testDb`.`testTable` 等同于  DESCRIBE `testDb`.`testTable`
     * @param string       $table
     * @param bool         $full
     * @param string|array $where
     * @return  string
     */
    public static function showTableColumns($table, $full = false, $where = null)
    {
        $query = static::getQuery();

        return static::build(
            'SHOW',
            $full ? 'FULL' : false,
            'COLUMNS FROM',
            $query->quoteName($table),
            $where ? new QueryElement('WHERE', $where, 'AND') : null
        );
    }

    // mysql: DESCRIBE `testTable`;
    static public function describeTable($table)
    {
        return "DESCRIBE `{$table}`";
    }

    static public function descTable($table)
    {
        return self::describeTable($table);
    }

    // mysql: SHOW CREATE TABLE `testDb`.`testTable`;
    /**
     * @param $table
     * @return string
     */
    static public function showCreateTable($table)
    {
        $query = static::getQuery();

        return 'SHOW CREATE TABLE ' . $query->quoteName($table);
    }

    /**
     * mysql: INSERT INTO `testDb`.`testTable` (... column1,column2 ... ) VALUES (...value1,value2...);
     * @param $table string e.g. testDb.test_table
     * @param $column
     * @param $values string|array
     * @return string
     */
    static public function createRecord($table, $column, $values)
    {
        $query = static::getQuery();

        return static::build(
            'INSERT INTO',
            $query->quoteName($table),
            "( $column )",
            "VALUES (" . ( is_array($values) ? implode('),(', $values) : $values) . ' )'
        );
    }

    /**
     * mysql: UPDATE `testDb`.`testTable` SET (... column1=value1,column2=value2...) WHERE ...;
     * @param $table string e.g. testDb.test_table
     * @param $keyValue string
     * @param $where string
     * @return string
     */
    static public function updateRecord($table, $keyValue, $where)
    {
        $query = static::getQuery();

        return static::build(
            'UPDATE',
            $query->quoteName($table),
            "SET $keyValue",
            stripos($where, 'WHERE ')===false ? "WHERE $where" : $where
        );
    }

    /**
     * mysql: DELETE FROM `testDb`.`testTable` WHERE id=xx...;
     * @param $table string e.g. testDb.test_table
     * @param $keyValue string
     * @param $where string
     * @return string
     */
    static public function deleteRecord($table, $where)
    {
        $query = static::getQuery();

        return static::build(
            'DELETE FROM',
            $query->quoteName($table),
            stripos($where, 'WHERE ')===false ? "WHERE $where" : $where
        );
    }
}